﻿CREATE PROCEDURE [dbo].[ListProjectsByIDs]
@ProjectIDs NVARCHAR (512), @UserID INT
AS
DECLARE @sql nvarchar(1024),
            @RoleID int
    
    SELECT @RoleID = RoleID
    FROM Users 
    WHERE UserID = @UserID
    
    IF (@RoleID = 1)
        BEGIN
        SET @sql = 'SELECT P.ProjectID,'
        SET @sql = @sql + ' P.Name AS ProjectName,'
        SET @sql = @sql + ' P.EstCompletionDate,'
        SET @sql = @sql + ' P.EstDuration AS EstHours,'
        SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
        SET @sql = @sql + 'FROM Projects P INNER JOIN EntryLog EL'
        SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
        SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
        SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'            
        END
    ELSE IF (@RoleID = 2)
        BEGIN
        SET @sql = 'SELECT P.ProjectID,'
        SET @sql = @sql + ' P.Name AS ProjectName,'
        SET @sql = @sql + ' P.EstCompletionDate,'
        SET @sql = @sql + ' P.EstDuration AS EstHours,'
        SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
        SET @sql = @sql + 'FROM Projects P INNER JOIN EntryLog EL'
        SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
        SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
        SET @sql = @sql + ' AND P.ManagerUserID = ' + CAST(@UserID AS nvarchar(20))
        SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'            
        END
    ELSE
        BEGIN
        SET @sql = 'SELECT P.ProjectID,'
        SET @sql = @sql + ' P.Name AS ProjectName,'
        SET @sql = @sql + ' P.EstCompletionDate,'
        SET @sql = @sql + ' P.EstDuration AS EstHours,'
        SET @sql = @sql + ' P.EstDuration AS ActualHours '
        SET @sql = @sql + 'FROM Projects P WHERE 1=0 '
        END
    
    EXEC sp_executesql @sql

